import pymysql
import logging
from typing import Dict, Any, Optional

# 导入配置管理工具
from utils.config_utils import get_db_config, get_config_env

# 配置日志
logger = logging.getLogger(__name__)

class DatabaseConnection:
    """数据库连接管理类"""
    
    def __init__(self, config=None, db_name=None):
        """
        初始化数据库连接
        
        Args:
            config: 数据库配置，如果为None则从配置文件加载
            db_name: 数据库名称，如果为None则默认使用freefishchn
        """
        # 从配置文件获取数据库配置，默认使用freefishchn
        self.config = config or get_db_config(db_name)
        self.connection = None
        self.cursor = None
    
    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(
                host=self.config.get('host', 'localhost'),
                port=self.config.get('port', 3306),
                user=self.config.get('user', 'root'),
                password=self.config.get('password', ''),
                database=self.config.get('database', 'freefishchn'),
                charset=self.config.get('charset', 'utf8mb4'),
                cursorclass=pymysql.cursors.DictCursor
            )
            self.cursor = self.connection.cursor()
            #logger.info(f"成功连接到数据库: {self.config.get('host')}:{self.config.get('port')}/{self.config.get('database')}")
            return True
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            self.connection = None
            self.cursor = None
            return False
    
    def close(self):
        """关闭数据库连接"""
        if self.cursor:
            try:
                self.cursor.close()
                self.cursor = None
            except Exception as e:
                logger.warning(f"关闭游标失败: {str(e)}")
        
        if self.connection:
            try:
                self.connection.close()
                self.connection = None
                logger.info("数据库连接已关闭")
            except Exception as e:
                logger.warning(f"关闭数据库连接失败: {str(e)}")
    
    def execute_query(self, query: str, params: tuple = None) -> list:
        """
        执行查询语句
        
        Args:
            query: SQL查询语句
            params: 查询参数
            
        Returns:
            list: 查询结果列表
        """
        if not self.connection or not self.cursor:
            if not self.connect():
                return []
        
        try:
            self.cursor.execute(query, params)
            result = self.cursor.fetchall()
            logger.debug(f"查询执行成功: {query}")
            return result
        except Exception as e:
            logger.error(f"查询执行失败: {query}, 错误: {str(e)}")
            return []
    
    def execute_update(self, query: str, params: tuple = None) -> int:
        """
        执行更新语句（INSERT, UPDATE, DELETE等）
        
        Args:
            query: SQL语句
            params: 查询参数
            
        Returns:
            int: 影响的行数
        """
        if not self.connection or not self.cursor:
            if not self.connect():
                return 0
        
        try:
            affected_rows = self.cursor.execute(query, params)
            self.connection.commit()
            logger.debug(f"更新执行成功: {query}, 影响行数: {affected_rows}")
            return affected_rows
        except Exception as e:
            logger.error(f"更新执行失败: {query}, 错误: {str(e)}")
            if self.connection:
                self.connection.rollback()
            return 0
    
    def begin_transaction(self):
        """开始事务"""
        if not self.connection:
            if not self.connect():
                return False
        
        try:
            self.connection.begin()
            logger.debug("事务已开始")
            return True
        except Exception as e:
            logger.error(f"开始事务失败: {str(e)}")
            return False
    
    def commit(self):
        """提交事务"""
        if not self.connection:
            return False
        
        try:
            self.connection.commit()
            logger.debug("事务已提交")
            return True
        except Exception as e:
            logger.error(f"事务提交失败: {str(e)}")
            return False
    
    def rollback(self):
        """回滚事务"""
        if not self.connection:
            return False
        
        try:
            self.connection.rollback()
            logger.debug("事务已回滚")
            return True
        except Exception as e:
            logger.error(f"事务回滚失败: {str(e)}")
            return False
    
    def __enter__(self):
        """支持上下文管理器协议"""
        self.connect()
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        """上下文管理器退出时关闭连接"""
        self.close()

# 创建全局数据库连接实例
def get_db_connection(config=None, db_name=None):
    """获取数据库连接实例的快捷函数
    
    Args:
        config: 数据库配置，如果为None则从配置文件加载
        db_name: 数据库名称，如果为None则默认使用freefishchn
        
    Returns:
        DatabaseConnection: 数据库连接实例
    """
    return DatabaseConnection(config, db_name)

def execute_sql(query: str, params: tuple = None) -> list:
    """执行SQL查询的快捷函数"""
    with get_db_connection() as db:
        return db.execute_query(query, params)

def execute_sql_update(query: str, params: tuple = None) -> int:
    """执行SQL更新的快捷函数"""
    with get_db_connection() as db:
        return db.execute_update(query, params)